﻿<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>阶段四模块二作业</title>
  <link rel="stylesheet" href="https://stackedit.io/style.css" />
</head>

<body class="stackedit">
  <div class="stackedit__html"><p>主要类&amp;方法&amp;参数&amp;返回值及代码行标注注释</p>
<p>基于user_id对c_order表进⾏数据分⽚</p>
<p>基于master1和master2主从集群实现读写分离</p>
<h1><a id="_6"></a>环境介绍</h1>
<p>虚拟机：VMware® Workstation 16 Pro<br>
操作系统： CentOs7<br>
数据库：mysql 5.7</p>
<ul>
<li>介绍各个机器对应角色&amp;作用&amp;ip地址</li>
</ul>
<p>MySQL集群（读写分离，分库分表）<br>
0：<br>
10.20.1.129（主）<br>
10.20.1.130（从）<br>
10.20.1.123（从）<br>
1：<br>
10.20.1.80（主）<br>
10.20.1.133（从）<br>
10.20.1.23（从）</p>
<h1><a id="_24"></a>运行效果</h1>
<h2><a id="user_idc_ordermaster1master2_26"></a>演示基于user_id对c_order表进⾏数据分⽚，基于master1和master2主从集群实现读写分离</h2>
<p>配置</p>
<pre><code>#数据库
spring.shardingsphere.datasource.names=master0,slave0,slave1,master1,slave2,slave3

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://10.20.1.80:3306/master0?useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=123456

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://10.20.1.133:3306/master0?useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://10.20.1.23:3306/master0?useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://10.20.1.129:3306/master1?useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=123456

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://10.20.1.130:3306/master1?useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://10.20.1.123:3306/master1?useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=123456

#分片规则
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$-&gt;{user_id % 2}
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$-&gt;{0..1}.c_order$-&gt;{0..1}
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$-&gt;{id % 2}

#id策略
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

#主从关系
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0, slave1
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2, slave3
</code></pre>
<h2><a id="Navicatsqlmaster1master2_88"></a>添加数据，通过Navicat展示数据分片成功，通过代码控制台真实sql展示数据分别在master1和master2进行写入</h2>
<p>循环插入十次，随机生成user_id<br>
<img src="https://img-blog.csdnimg.cn/f058fb0c9b8c483f8025734858dea332.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
<p><img src="https://img-blog.csdnimg.cn/cd62b4bb2608412980845d8848b338c3.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
<p><img src="https://img-blog.csdnimg.cn/32364188dfe84a3eaf0e6dd8b3c9b2f5.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"><br>
<img src="https://img-blog.csdnimg.cn/f3de7195373e4bedba5323acbded5317.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
<p><img src="https://img-blog.csdnimg.cn/11c50f15321f4a57bb1b524a75a4a414.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"><br>
<img src="https://img-blog.csdnimg.cn/a5eb027c320843afb6911136b8971937.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
<h2><a id="sql4_102"></a>查询数据，通过代码控制台真实sql展示数据是通过4个从节点查询的</h2>
<p>查询所有数据</p>
<p><img src="https://img-blog.csdnimg.cn/e234dee9d0504b648b21d093067b3cdf.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
<p><img src="https://img-blog.csdnimg.cn/baea64e3f26b4145a3b0797b615d25c5.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"><br>
在从库中读取拼装数据<br>
<img src="https://img-blog.csdnimg.cn/6d6d20349baa4b43a562a2751f8d25c7.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAR0pPRUQ=,size_20,color_FFFFFF,t_70,g_se,x_16" alt="在这里插入图片描述"></p>
</div>
</body>

</html>
